package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.amado.easyui2.dao.UserDao;
import com.amado.easyui2.domain.User;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

public class UserDaoOracleImpl implements UserDao {

	
	@Override
	public int add(User user) {
		Connection conn = DBConnection.getConn();    //emp_use_ID.nextVal id自动增加
		
		String sql = "insert into sys_user(use_ID ,dept_Num ,user_Num ,user_password ,create_Time ,update_Time,create_Name,update_Name,delete_Flag)values(emp_use_ID.nextVal,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, user.getDepartmentNum());
			ps.setString(i++, user.getUserNum());
			ps.setString(i++, user.getPassword());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, user.getCreateName());
			ps.setString(i++, user.getUpdateName());
			ps.setString(i++, user.getDeleteFlag());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int delete(Long id) {
		Connection conn = DBConnection.getConn();
		String sql = "update sys_user set delete_Flag='Y' where use_ID=?";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setLong(i, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int update(User user) {
		Connection conn = DBConnection.getConn();
		String sql = "update sys_user set dept_Num=?,user_Num=?,user_password=?,create_Time=?,update_Time=?,create_Name=?,update_Name=? where use_ID =?";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, user.getDepartmentNum());
			ps.setString(i++, user.getUserNum());
			ps.setString(i++, user.getPassword());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, user.getCreateName());
			ps.setString(i++, user.getUpdateName());
			ps.setLong(i++, user.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	
	@Override
	public int listAllCount(String departNum, String userName) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM sys_user where delete_flag='N'";
		if (userName != null && userName.trim() != "") {
			sql = sql + " and user_Num like ?";
		}
		if (departNum != null && departNum.trim() != "") {
			sql = sql + " and dept_Num =?";
		}
		System.out.println("sql"+sql);
		List<User> list = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (departNum != null && departNum.trim() != "") {
				ps.setString(j++, departNum);
			}
			if (userName != null && userName.trim() != "") {
				ps.setString(j++, "%"+userName+"%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}
	@Override
	public List<User> listAll(String userName, String departNum, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sqlss ="select * from t_user where id=11 @sql@ group by name";
		
		StringBuffer sb = new StringBuffer();
		if(userName!=null) {
			sb.append(" and userName ='").append(userName).append("'");
		}
		sqlss.replace("@sql@", sb.toString());
		
		
		String sql = "SELECT * FROM sys_user where delete_flag='N'";
		
		if (departNum != null && departNum.trim() != "") {
			sql = sql + " and dept_Num =?";
		}
		if (userName != null && userName.trim() != "") {
			sql = sql + " and user_Num like ?";
		}
		System.out.println("sql"+sql);
		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("SELECT * FROM  (SELECT A.*, ROWNUM RN FROM (");
		sqlSb.append(sql);
		sqlSb.append(") A WHERE ROWNUM <= ? ) WHERE RN > ? ");
		System.out.println(sqlSb.toString());
		List<User> list = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sqlSb.toString());
			int j = 1;
			if (departNum != null && departNum.trim() != "") {
				ps.setString(j++, departNum);
			}
			if (userName != null && userName.trim() != "") {
				ps.setString(j++, "%"+userName+"%");
			}
			ps.setInt(j++,page.getCurrentPage()*page.getRowSize());
			ps.setInt(j++,(page.getCurrentPage()-1)*page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String userNum = rs.getString(i++);
				String password = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(
						new User(id, departmentNum, userNum, password, createTime, updateTime, createName, updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<User> list() {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM sys_user where delete_flag='N'";
		List<User> list = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String userNum = rs.getString(i++);
				String password = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(new User(id, departmentNum, userNum, password, createTime, updateTime, createName, updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, null, conn);
		}
		return list;
	}

	@Override
	public List<User> login(User user)
	{
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM sys_user where user_Num=? and user_password=? ";
		List<User> list = new ArrayList<User>();
		try
		{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				int i = 1;
				String userNum = rs.getString(i++);
				String password = rs.getString(i++);
				list.add(new User(userNum,password));
			}
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public User getBy(String userName) {
		// TODO Auto-generated method stub
		return null;
	}


}
